﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using Entidad;
using System.Data.SqlClient;
using System.Data;

namespace Datos
{
    public class DaUsuario
    {
        public int updateUsuario(string Usuario, string password)
        {
            string cadCon = ConfigurationManager.AppSettings["connString"].ToString();
            int res = 0;
            using (SqlConnection cn = new SqlConnection(cadCon))
            {
                try
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand("proc_Usuario_up", cn))
                    {
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;
                        cmd.Parameters.Add("@Usuario", SqlDbType.NVarChar).Value = Usuario;
                        cmd.Parameters.Add("@password", SqlDbType.NVarChar).Value = password;
                        res = Convert.ToInt16(cmd.ExecuteScalar());
                    }
                }
                catch
                {
                    res = 0;
                }

            }

            return res;
        }

        public EnUsuario validaLogin(string Usuario, string password)
        {
            string cadCon = ConfigurationManager.AppSettings["connString"].ToString();
            SqlDataReader dr1;
            EnUsuario objR = null;
            using (SqlConnection cn = new SqlConnection(cadCon))
            {
                cn.Open();
                using (SqlCommand cmd = new SqlCommand("proc_login_intra_se", cn))
                {
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.Add("@Usuario", SqlDbType.NVarChar).Value = Usuario;
                    cmd.Parameters.Add("@password", SqlDbType.NVarChar).Value = password;

                    dr1 = cmd.ExecuteReader();
                    while (dr1.Read())
                    {
                        objR = new EnUsuario();
                        objR.usuario = dr1[0].ToString();
                        objR.nombre = dr1[1].ToString();
                        objR.apellido = dr1[2].ToString();
                        objR.tipo = dr1[3].ToString();
                        objR.vez = dr1[4].ToString();
                    }
                    dr1.Close();
                }
            }

            return objR;
        }


        public List<EnCombo> getUsuarios()
        {
            List<EnCombo> lst = new List<EnCombo>();
            EnCombo it = new EnCombo();
            it.codigo = "";
            it.decrip = "Seleccione un usuario";
            lst.Add(it);
            try
            {
                string cadCon = ConfigurationManager.AppSettings["connString"].ToString();
                SqlDataReader dr1;
                using (SqlConnection cn = new SqlConnection(cadCon))
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand("proc_usuarios_combo_se", cn))
                    {
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;

                        dr1 = cmd.ExecuteReader();
                        while (dr1.Read())
                        {
                            EnCombo item = new EnCombo();
                            item.codigo = dr1[0].ToString();
                            item.decrip = dr1[1].ToString();
                            lst.Add(item);
                        }
                        dr1.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                lst = new List<EnCombo>();
            }

            return lst;
        }

        public List<EnCombo> getSociedadesNoAsigXUsuario(String usuario)
        {
            List<EnCombo> lst = new List<EnCombo>();
            try
            {
                string cadCon = ConfigurationManager.AppSettings["connString"].ToString();
                SqlDataReader dr1;
                using (SqlConnection cn = new SqlConnection(cadCon))
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand("proc_sociedad_no_asignada_se", cn))
                    {
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;
                        cmd.Parameters.Add("@usuario", SqlDbType.NVarChar).Value = usuario;

                        dr1 = cmd.ExecuteReader();
                        while (dr1.Read())
                        {
                            EnCombo item = new EnCombo();
                            item.codigo = dr1[0].ToString();
                            item.decrip = dr1[1].ToString();
                            lst.Add(item);
                        }
                        dr1.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                lst = new List<EnCombo>();
            }

            return lst;
        }

        public List<EnCombo> getSociedadesSiAsigXUsuario(String usuario, String tipo)
        {
            List<EnCombo> lst = new List<EnCombo>();
            try
            {
                string cadCon = ConfigurationManager.AppSettings["connString"].ToString();
                SqlDataReader dr1;
                using (SqlConnection cn = new SqlConnection(cadCon))
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand("proc_sociedad_si_asignada_se", cn))
                    {
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;
                        cmd.Parameters.Add("@usuario", SqlDbType.NVarChar).Value = usuario;
                        if (tipo.Equals("c")) //data para un combo
                        {
                            EnCombo item = new EnCombo();
                            item.codigo = "";
                            item.decrip = "";
                            lst.Add(item);                        
                        }

                        dr1 = cmd.ExecuteReader();
                        while (dr1.Read())
                        {
                            EnCombo item = new EnCombo();
                            item.codigo = dr1[0].ToString();
                            item.decrip = dr1[1].ToString();
                            lst.Add(item);
                        }
                        dr1.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                lst = new List<EnCombo>();
            }

            return lst;
        }

        public int setSociedadesXusu(List<EnCombo> soXusu, String usuario)
        {
            string cadCon = ConfigurationManager.AppSettings["connString"].ToString();
            int res = 0;
            using (SqlConnection cn = new SqlConnection(cadCon))
            {
                try
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand("proc_sociedad_asignada_de", cn))
                    {
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;
                        cmd.Parameters.Add("@Usuario", SqlDbType.NVarChar).Value = usuario;
                        res = Convert.ToInt16(cmd.ExecuteScalar());
                    }

                    if (res == 0) //exito
                    {
                        //asigno centros
                        foreach (EnCombo it in soXusu)
                        {
                            using (SqlCommand cmd = new SqlCommand("proc_asignar_sociedades_in", cn))
                            {
                                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                cmd.Parameters.Add("@Usuario", SqlDbType.NVarChar).Value = usuario;
                                cmd.Parameters.Add("@sociedad", SqlDbType.NVarChar).Value = it.codigo;
                                res = Convert.ToInt16(cmd.ExecuteScalar());
                                if(res != 0)
                                {
                                    break;
                                }
                            }
                        }
                    }
                }
                catch
                {
                    res = 0;
                }

            }

            return res;
        }



        public List<EnUsuario> getLstUsuarios()
        {
            List<EnUsuario> lst = new List<EnUsuario>();
            try
            {
                string cadCon = ConfigurationManager.AppSettings["connString"].ToString();
                SqlDataReader dr1;
                using (SqlConnection cn = new SqlConnection(cadCon))
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand("proc_usuarios_se", cn))
                    {
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;

                        dr1 = cmd.ExecuteReader();
                        while (dr1.Read())
                        {
                            EnUsuario item = new EnUsuario();
                            item.usuario = dr1[0].ToString();
                            item.nombre = dr1[2].ToString();
                            item.apellido = dr1[3].ToString();
                            item.tipo = dr1[4].ToString();
                            if (item.tipo.Equals("1"))
                            {
                                item.desctipo = "Administrador";
                            }
                            else
                            {
                                item.desctipo = "No Administrador";
                            }
                            lst.Add(item);
                        }
                        dr1.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                lst = new List<EnUsuario>();
            }

            return lst;
        }


        public int setUsuario(EnUsuario usuario)
        {
            string cadCon = ConfigurationManager.AppSettings["connString"].ToString();
            int res = 0;
            using (SqlConnection cn = new SqlConnection(cadCon))
            {
                try
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand("proc_usuarios_in", cn))
                    {
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;
                        cmd.Parameters.Add("@Usuario", SqlDbType.NVarChar).Value = usuario.usuario.ToUpper();
                        cmd.Parameters.Add("@nombre", SqlDbType.NVarChar).Value = usuario.nombre.ToUpper();
                        cmd.Parameters.Add("@apellido", SqlDbType.NVarChar).Value = usuario.apellido.ToUpper();
                        cmd.Parameters.Add("@tipo", SqlDbType.NVarChar).Value = usuario.tipo;
                        res = Convert.ToInt16(cmd.ExecuteScalar());
                    }
                }
                catch
                {
                    res = 0;
                }

            }

            return res;
        }

        public int updUsuario(EnUsuario usuario)
        {
            string cadCon = ConfigurationManager.AppSettings["connString"].ToString();
            int res = 0;
            using (SqlConnection cn = new SqlConnection(cadCon))
            {
                try
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand("proc_usuarios_up", cn))
                    {
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;
                        cmd.Parameters.Add("@Usuario", SqlDbType.NVarChar).Value = usuario.usuario;
                        cmd.Parameters.Add("@nombre", SqlDbType.NVarChar).Value = usuario.nombre;
                        cmd.Parameters.Add("@apellido", SqlDbType.NVarChar).Value = usuario.apellido;
                        cmd.Parameters.Add("@tipo", SqlDbType.NVarChar).Value = usuario.tipo;
                        res = Convert.ToInt16(cmd.ExecuteScalar());
                    }
                }
                catch
                {
                    res = 0;
                }

            }

            return res;
        }


        public int delUsuario(String usuario)
        {
            string cadCon = ConfigurationManager.AppSettings["connString"].ToString();
            int res = 0;
            using (SqlConnection cn = new SqlConnection(cadCon))
            {
                try
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand("proc_usuarios_de", cn))
                    {
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;
                        cmd.Parameters.Add("@Usuario", SqlDbType.NVarChar).Value = usuario;
                        res = Convert.ToInt16(cmd.ExecuteScalar());
                    }
                }
                catch
                {
                    res = 0;
                }

            }

            return res;
        }
    }
}
